#!/use/bin/python
# coding=utf-8
# 数据分析-助眠功能-活跃数
import datetime
import pymysql
from dateutil.relativedelta import relativedelta

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    if num is None:
        return 0
    return num

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 助眠功能活跃用户数-日  analysis_zmgn_active_number     每天
def insertActiveNumberDay(today):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zmgn_active_number 表是否已经存在当天 数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_zmgn_active_number t where 1=1 and t.ref_date =  '{today}' and date_type = 1")
        # 获取助眠功能活跃用户数
        deviceNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) device_num from log_analysis t where 1=1 and DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{today}'")
        activeNum = selectNumBySql(
            f"select count(1) count_num from log_analysis t where 1=1 and DATE_FORMAT(t.create_time, '%Y-%m-%d') = '{today}'")


        if countNum == 0:
            logDetail = f"新增-{today}日-助眠功能活跃用户数,日活次数为{activeNum},日活用户数为{deviceNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_zmgn_active_number(active_num,active_device_num,ref_date,date_type) 
                value({activeNum},{deviceNum},'{today}',1)"""
            cur.execute(insertSql)
        else:
            logDetail = f"更新-{today}日-助眠功能活跃用户数,日活次数为{activeNum},日活用户数为{deviceNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_zmgn_active_number set active_num = {activeNum},active_device_num = {deviceNum} ,update_time = now()  where  ref_date = '{today}' and date_type = 1
            """
            cur.execute(updateSql)
        saveLog(logDetail, today)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 助眠功能活跃用户数-月  analysis_zmgn_active_number     每月
def insertActiveNumberMonth(toMonthDay,beforeMonthDay,beforeYearDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zmgn_active_number 表是否已经存在当月数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_zmgn_active_number t where 1=1 and t.ref_date =  '{toMonthDay}' and date_type = 3")
        # 获取助眠功能活跃用户数
        deviceNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) device_num from log_analysis t where 1=1 and DATE_FORMAT(t.create_time, '%Y-%m') = '{toMonthDay}'")
        activeNum = selectNumBySql(
            f"select count(1) count_num from log_analysis t where 1=1 and DATE_FORMAT(t.create_time, '%Y-%m') = '{toMonthDay}'")

        monthOnMonthNum = selectNumBySql(
            f"select active_device_num from analysis_zmgn_active_number t  where  t.ref_date = '{beforeMonthDay}' and date_type = 3")
        #上一个年相同月份 计算同比
        yearOnYearNum = selectNumBySql(
            f"select active_device_num from analysis_zmgn_active_number t  where  t.ref_date = '{beforeYearDay}' and date_type = 3")
        # 计算服务次数及同比环比
        monthOnMonth = getRateByXY(deviceNum-monthOnMonthNum,monthOnMonthNum)
        yearOnYear = getRateByXY(deviceNum-yearOnYearNum,yearOnYearNum)

        if countNum == 0:
            logDetail = f"新增-{toMonthDay}月-助眠功能活跃用户数,月活跃次数为{activeNum},月活跃用户数{deviceNum},去年当月活跃用户数为{yearOnYearNum},环比为{yearOnYear},生活助手上月活跃用户数为{monthOnMonthNum},同比为{monthOnMonth}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_zmgn_active_number(active_num,active_device_num,year_on_year,month_on_month,ref_date,date_type) 
                            value({activeNum},{deviceNum},{yearOnYear},{monthOnMonth},'{toMonthDay}',3)"""
            cur.execute(insertSql)
        else:
            logDetail = f"修改-{toMonthDay}月-助眠功能活跃用户数,月活跃次数为{activeNum},月活跃用户数{deviceNum},去年当月活跃用户数为{yearOnYearNum},环比为{yearOnYear},生活助手上月活跃用户数为{monthOnMonthNum},同比为{monthOnMonth}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_zmgn_active_number set active_num = {activeNum},active_device_num = {deviceNum} ,
                year_on_year = {yearOnYear},month_on_month = {monthOnMonth},update_time = now()  where ref_date = '{toMonthDay}'  and date_type = 3
            """
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 助眠功能活跃用户数-周  analysis_zmgn_active_number  每周
def insertActiveNumberWeek(toDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zmgn_active_number 表是否已经存在当周数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_zmgn_active_number t where 1=1 and weekofyear(t.ref_date) = weekofyear('{toDay}')  and date_type = 2 ")
        # 获取 当周助眠功能活跃用户数(未去重)
        serviceNum = selectNumBySql(
            f"""select sum(t.active_device_num) from analysis_zmgn_active_number t where t.date_type = 1 and LENGTH(t.ref_date) = 10 
                                      and weekofyear(t.ref_date) = weekofyear('{toDay}') """)
        # 获取助眠功能活跃用户数
        deviceNum = selectNumBySql(
            f"select count(DISTINCT(t.active_id)) device_num from log_analysis t where 1=1 and weekofyear(DATE_FORMAT(t.create_time, '%Y-%m-%d')) = weekofyear('{toDay}')")
        activeNum = selectNumBySql(
            f"select count(1) count_num from log_analysis t where 1=1 and weekofyear(DATE_FORMAT(t.create_time, '%Y-%m-%d')) = weekofyear('{toDay}')")

        # 获取 上一周周因生活助手开机次数 计算环比
        monthOnMonthNum = selectNumBySql(
            f"""
                    select t.active_device_num from analysis_zmgn_active_number t where t.date_type = 2 
                        and weekofyear(t.ref_date) = weekofyear('{toDay}') -1 """)
        # 上一个年相同周 因生活助手开机次数 计算同比
        yearOnYearNum = selectNumBySql(
            f"""
                            select t.active_device_num from analysis_zmgn_active_number t where t.date_type = 2  
                                and weekofyear(t.ref_date) = weekofyear('{toDay}') and year(t.ref_date) =  year('{toDay}') - 1""")
        # 计算 同比环比
        monthOnMonth = getRateByXY(deviceNum - monthOnMonthNum, monthOnMonthNum)
        yearOnYear = getRateByXY(deviceNum - yearOnYearNum, yearOnYearNum)

        if countNum == 0:
            logDetail = f"新增-{toDay}周-助眠功能活跃用户数,周活跃次数为{activeNum},周活跃用户数{deviceNum},去年当周活跃用户数为{yearOnYearNum},环比为{yearOnYear},生活助手上周活跃用户数为{monthOnMonthNum},同比为{monthOnMonth}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_zmgn_active_number(active_num,active_device_num,year_on_year,month_on_month,ref_date,date_type) 
                                   value({activeNum},{deviceNum},{yearOnYear},{monthOnMonth},'{toDay}',2)"""
            cur.execute(insertSql)
        else:
            logDetail = f"修改-{toDay}周-助眠功能活跃用户数,周活跃次数为{activeNum},月活跃用户数{deviceNum},去年当周活跃用户数为{yearOnYearNum},环比为{yearOnYear},生活助手上周活跃用户数为{monthOnMonthNum},同比为{monthOnMonth}"
            print(logDetail)
            # 更新
            updateSql = f"""
                       update analysis_zmgn_active_number set active_num = {activeNum},active_device_num = {deviceNum} ,
                       year_on_year = {yearOnYear},month_on_month = {monthOnMonth},update_time = now()  where weekofyear(ref_date) = weekofyear('{toDay}')  and date_type = 2
                   """
            cur.execute(updateSql)
        saveLog(logDetail, toDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

# 获取上一个月
def getBeforeMonth(d):
    today = datetime.datetime.now()
    re_date = (today + relativedelta(months=-d)).strftime("%Y-%m")
    return re_date

# 获取上一个年当月
def getBeforeYear():
    today = datetime.datetime.now()
    re_date = (today + relativedelta(years=-1)).strftime("%Y-%m")
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 94
        jobName = '核心指标-助眠功能-活跃用户数'
        tableName = 'analysis_zmgn_active_number'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

def mainAll(d):
    today = getdate(d)
    toMonthDay = getMonthdate(d)
    beforeMonthDay = getBeforeMonth(1)
    beforeYearDay = getBeforeYear()
    print(f"{today}日期,执行<助眠功能-活跃用户数>报表")
    #insertActiveNumberDay(today)
    # insertActiveNumberMonth(toMonthDay, beforeMonthDay, beforeYearDay)
    insertActiveNumberWeek(today)

if __name__ == '__main__':
    d = 8
    mainAll(d)

